RootZanli & NinjaCr3

: / home / o5t6x7pgljbm / public_html / admin / app / V2 / BulkOperations / Importers /
Filename : UsersImporter.php
back
<?php

namespace App\V2\BulkOperations\Importers;

use App\Libraries\FileStorageSystem;
use App\V2\BulkOperations\ImportFactories\FieldValidatorFactory;
use App\V2\BulkOperations\ImportFields\DataImportField;
use App\V2\BulkOperations\ImportFieldTransformers\DownloadImageFieldTransformer;
use App\V2\BulkOperations\ImportFieldTransformers\PasswordHashFieldTransformer;
use App\V2\BulkOperations\ImportFieldTransformers\PreferredSchoolIdFieldTransformer;
use App\V2\BulkOperations\ImportFieldTransformers\RoleIdFieldTransformer;
use App\V2\BulkOperations\ImportFieldTransformers\StudentSegmentIdTransformer;
use App\V2\BulkOperations\ImportServices\ImportLoggingService;
use App\V2\BulkOperations\ImportServices\TempTableService;
use Illuminate\Support\Facades\Config;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;

class UsersImporter extends BaseImporter
{
    private $fields = [];
    private $additional_fields = [];

    public function __construct($importProfile)
    {
        parent::__construct($importProfile);
    }

    private function populateFields(){
        $this->fields['full_name'] = new DataImportField('full_name','full_name', 'VARCHAR(255)',
            [
                // FieldValidatorFactory::getFieldValidator(FieldValidatorFactory::STRING_VALIDATOR_0_255_ALPHANUM),
                FieldValidatorFactory::getFieldValidator(FieldValidatorFactory::NOT_NULL_VALIDATOR),
            ]);
        $this->fields['profile_pic_url'] = new DataImportField('profile_pic_url','profile_pic_url', 'TEXT',
            [
                FieldValidatorFactory::getFieldValidator(FieldValidatorFactory::STRING_VALIDATOR_URL),
            ]);
        $this->fields['email'] = new DataImportField('email','email', 'VARCHAR(255)',
        [
            FieldValidatorFactory::getFieldValidator(FieldValidatorFactory::STRING_VALIDATOR_0_255_EMAIL),
            FieldValidatorFactory::getFieldValidator(FieldValidatorFactory::NOT_NULL_VALIDATOR),
        ]);
        $this->fields['mobile'] = new DataImportField('mobile','mobile', 'VARCHAR(255)',
        [
            FieldValidatorFactory::getFieldValidator(FieldValidatorFactory::STRING_VALIDATOR_0_255_MOBILE),
            FieldValidatorFactory::getFieldValidator(FieldValidatorFactory::NOT_NULL_VALIDATOR),
        ]);
        $this->fields['password'] = new DataImportField('password','password', 'VARCHAR(255)',
        [
            FieldValidatorFactory::getFieldValidator(FieldValidatorFactory::STRING_VALIDATOR_0_255_PASSWORD),
            FieldValidatorFactory::getFieldValidator(FieldValidatorFactory::NOT_NULL_VALIDATOR),
        ]);
        $this->fields['user_role'] = new DataImportField('user_role','user_role', 'VARCHAR(255)',
        [
            FieldValidatorFactory::getFieldValidator(FieldValidatorFactory::USER_ROLE_VALIDATOR),
            FieldValidatorFactory::getFieldValidator(FieldValidatorFactory::NOT_NULL_VALIDATOR),
        ]);
        $this->fields['group_uuid'] = new DataImportField('group_uuid','group_uuid', 'VARCHAR(255)',
        [
            FieldValidatorFactory::getFieldValidator(FieldValidatorFactory::GROUP_UUID_VALIDATOR),
            FieldValidatorFactory::getFieldValidator(FieldValidatorFactory::NOT_NULL_VALIDATOR),
        ]);
        $this->fields['preferred_school'] = new DataImportField('preferred_school','preferred_school', 'VARCHAR(255)',
        []);
        $this->fields['address_line_1'] = new DataImportField('address_line_1','address_line_1', 'TEXT',[]);
        $this->fields['address_line_2'] = new DataImportField('address_line_2','address_line_2', 'TEXT',[]);
        $this->fields['address_line_3'] = new DataImportField('address_line_3','address_line_3', 'TEXT',[]);
        $this->fields['city'] = new DataImportField('city','city', 'VARCHAR(255)',[]);
        $this->fields['state'] = new DataImportField('state','state', 'VARCHAR(255)',[]);
        $this->fields['country_name'] = new DataImportField('country_name','country_name', 'VARCHAR(250)',[
            // FieldValidatorFactory::getFieldValidator(FieldValidatorFactory::COUNTRY_NAME_VALIDATOR),
            FieldValidatorFactory::getFieldValidator(FieldValidatorFactory::NOT_NULL_VALIDATOR),
        ]);
        $this->fields['zipcode'] = new DataImportField('zipcode','zipcode', 'VARCHAR(255)',[
            // FieldValidatorFactory::getFieldValidator(FieldValidatorFactory::ZIPCODE_VALIDATOR),
            FieldValidatorFactory::getFieldValidator(FieldValidatorFactory::NOT_NULL_VALIDATOR),
        ]);
        $this->fields['student_segment_name_1'] = new DataImportField('student_segment_name_1','student_segment_name_1', 'VARCHAR(255)',[]);
        $this->fields['student_segment_name_2'] = new DataImportField('student_segment_name_2','student_segment_name_2', 'VARCHAR(255)',[]);
        $this->fields['student_segment_name_3'] = new DataImportField('student_segment_name_3','student_segment_name_3', 'VARCHAR(255)',[]);

        //other fields to be populated with default value or with transformed values:
        $this->fields['tuit_wallet_uid'] = new DataImportField('tuit_wallet_uid', 'tuit_wallet_uid', 'VARCHAR(255)', []);
        $this->fields['sp_wallet_uid'] = new DataImportField('sp_wallet_uid', 'sp_wallet_uid', 'VARCHAR(255)', []);
        $this->fields['user_uuid'] = new DataImportField('user_uuid', 'user_uuid', 'VARCHAR(255)', []);
        $this->fields['api_token'] = new DataImportField('api_token', 'api_token', 'VARCHAR(255)', []);
        $this->fields['password_hash'] = new DataImportField('password_hash', 'password_hash', 'VARCHAR(255)', []);
        $this->fields['local_profile_pic_url'] = new DataImportField('local_profile_pic_url', 'local_profile_pic_url', 'VARCHAR(255)', []);
        $this->fields['role_id'] = new DataImportField('role_id', 'role_id', 'INT', []);
        $this->fields['preferred_school_id'] = new DataImportField('preferred_school_id', 'preferred_school_id', 'INT', [
            FieldValidatorFactory::getFieldValidator(FieldValidatorFactory::PREFERRED_SCHOOL_VALIDATOR),
        ]);
        $this->fields['student_segment_name_1_id'] = new DataImportField('student_segment_name_1_id', 'student_segment_name_1_id', 'INT', []);
        $this->fields['student_segment_name_2_id'] = new DataImportField('student_segment_name_2_id', 'student_segment_name_2_id', 'INT', []);
        $this->fields['student_segment_name_3_id'] = new DataImportField('student_segment_name_3_id', 'student_segment_name_3_id', 'INT', []);


        $this->fields['full_name']->setIsRequired(true);
        $this->fields['email']->setIsRequired(true);
        $this->fields['mobile']->setIsRequired(true);
        $this->fields['password']->setIsRequired(true);
        $this->fields['user_role']->setIsRequired(true);
        $this->fields['group_uuid']->setIsRequired(true);
        $this->fields['country_name']->setIsRequired(true);
        $this->fields['zipcode']->setIsRequired(true);

        $this->fields['user_uuid']->setAutoGenerateType(DataImportField::AUTO_GENERATE_TYPE_INT_UUID);
        $this->fields['api_token']->setAutoGenerateType(DataImportField::AUTO_GENERATE_TYPE_API_TOKEN);
        $this->fields['tuit_wallet_uid']->setAutoGenerateType(DataImportField::AUTO_GENERATE_TYPE_STR_UUID);
        $this->fields['sp_wallet_uid']->setAutoGenerateType(DataImportField::AUTO_GENERATE_TYPE_STR_UUID);
        $this->fields['password_hash']->setTransformer(new PasswordHashFieldTransformer($this->fields['password_hash'], [$this->fields['password']]));
        $this->fields['role_id']->setTransformer(new RoleIdFieldTransformer($this->fields['role_id'], [$this->fields['user_role']]));
        $this->fields['preferred_school_id']->setTransformer(new PreferredSchoolIdFieldTransformer($this->fields['preferred_school_id'], [$this->fields['preferred_school']]));
        $this->fields['local_profile_pic_url']->setTransformer(
            new DownloadImageFieldTransformer($this->fields['local_profile_pic_url'],
            [$this->fields['profile_pic_url']], 'profile_pics',
            'default-profile-image-color.jpg') );
        $this->fields['student_segment_name_1_id']->setTransformer(new StudentSegmentIdTransformer($this->fields['student_segment_name_1_id'], [$this->fields['preferred_school_id'], $this->fields['student_segment_name_1']]));
        $this->fields['student_segment_name_2_id']->setTransformer(new StudentSegmentIdTransformer($this->fields['student_segment_name_2_id'], [$this->fields['preferred_school_id'], $this->fields['student_segment_name_2']]));
        $this->fields['student_segment_name_3_id']->setTransformer(new StudentSegmentIdTransformer($this->fields['student_segment_name_3_id'], [$this->fields['preferred_school_id'], $this->fields['student_segment_name_3']]));

    }

    private function populateProfileSpecificAdditionalFields(){
        $this->additional_fields['user_id'] = new DataImportField('user_id', 'user_id', 'INT', []);
        $this->additional_fields['user_address_id'] = new DataImportField('user_address_id', 'user_address_id', 'INT', []);
        $this->additional_fields['tuit_wallet_id'] = new DataImportField('tuit_wallet_id', 'tuit_wallet_id', 'INT', []);
        $this->additional_fields['sp_wallet_id'] = new DataImportField('sp_wallet_id', 'sp_wallet_id', 'INT', []);
        $this->additional_fields['coins_for_college_role_id'] = new DataImportField('coins_for_college_role_id', 'coins_for_college_role_id', 'INT', []);
        $this->additional_fields['group_id'] = new DataImportField('group_id', 'group_id', 'INT', []);
        $this->additional_fields['team_id'] = new DataImportField('team_id', 'team_id', 'INT', []);
    }

    protected function getFields(): array {
        if(count($this->fields) == 0)
            $this->populateFields();
        return $this->fields;
    }

    protected function getProfileSpecificAdditionalFields(): array
    {
        if(count($this->additional_fields) == 0)
            $this->populateProfileSpecificAdditionalFields();
        return $this->additional_fields;
    }

    protected function getMainTableId(){
        return 'user_id';
    }

    protected function validateProfileSpecificData()
    {
        // Validation logic
        // 1, validate mobile duplicate in temp table and in users table.
        $this->validateMobileDuplicate();

        // 2, validate email duplicate in temp table and in users table.
        $this->validateEmailDuplicate();

        // 3, validate user role and populate user role id
        $this->validateRole();
        $this->populateRoleId();

        // 4, validate group and populate group id
        $this->validateGroupIfPresent();
        $this->populateGroupId();

        // 5, populate error if school is not valid
        // $this->validateSchoolDetails();
    }

    public function importToMainProfileSpecificData()
    {
        //Insert into main tables
        // 1, insert into users table
        $this->insertIntoUsers();
        $this->populateUserIdToTempTable();

        // 2, insert into users_address
        $this->insertIntoUserAddress();

        // 3, insert into user_settings
        $this->insertIntoUserSettings();

        // 4, insert into group_members
        $this->insertIntoGroupMembers();

        // 5, insert into role_user
        $this->insertIntoRoleUser();

        // 6, insert into sp_wallet
        // $this->insertIntoSpWallet();
        // $this->populateSpWalletIdToTempTable();

        // 7, insert into tuit_wallet
        // $this->insertIntoTuitWallet();
        // $this->populateTuitWalletIdToTempTable();

        // 8, insert into StudentSegment
        $this->insertIntoStudentSegmentUsers('student_segment_name_1_id');
        $this->insertIntoStudentSegmentUsers('student_segment_name_2_id');
        $this->insertIntoStudentSegmentUsers('student_segment_name_3_id');
    }

    private function insertIntoUsers(){
        //TODO: update password, api_token and profile_pic, user_uuid
        $validate_is_valid_field_name = $this->getAllFields()['validate_is_valid']->getColumnName();
        $total_records = $this->importQueueItem->additionalDetails->total_records;
        $insert_sql = "INSERT INTO `users`(uuid, full_name, profile_pic, email, mobile, "
            . " password, coins_for_college_role_id, api_token, is_email_verified, "
            . " active_group_id, created_at, import_queue_item_id, preferred_school_id) ";
        $select_sql = " SELECT user_uuid, full_name, local_profile_pic_url, email, mobile, "
            . " password_hash, coins_for_college_role_id, null AS api_token, 0 AS is_email_verified,"
            . " group_id, NOW(), {$this->importQueueItem->import_queue_item_id} AS import_queue_item_id , preferred_school_id "
            . " FROM {$this->importQueueItem->temp_table_name} AS tmp ";
        $select_sql .= " WHERE tmp.{$validate_is_valid_field_name} = 1 ";

        $this->tempTableService->executeUpdateInBatches($insert_sql . $select_sql, $this->pk_column_name, $this->batch_size, $total_records );
    }

    protected function populateUserIdToTempTable(){
        $field = $this->getAllFields()['user_uuid'];
        $ref_id_field = $this->getAllFields()['user_id'];

        $total_records = $this->importQueueItem->additionalDetails->total_records;

        $validationQuery = "UPDATE {$this->importQueueItem->temp_table_name} AS tmp ";
        $validationQuery .= " LEFT JOIN `users` AS u  ";
        $validationQuery .= " ON u.uuid = tmp.{$field->getColumnName()} " ;
        $validationQuery .= " SET tmp.{$ref_id_field->getColumnName()} = u.user_id ";
        $validationQuery .= " WHERE u.user_id IS NOT NULL AND u.deleted_at IS NULL ";

        Log::debug("Running Update Query: ". $validationQuery);
        $this->tempTableService->executeUpdateInBatches($validationQuery,
            'tmp.'.$this->pk_column_name, $this->batch_size, $total_records);
        return ['error' => false, 'error_message' => ''];
    }

    private function insertIntoUserAddress(){
        $validate_is_valid_field_name = $this->getAllFields()['validate_is_valid']->getColumnName();
        $total_records = $this->importQueueItem->additionalDetails->total_records;
        $insert_sql = "INSERT INTO `users_address`(user_id, address_line_1, address_line_2, address_line_3, "
            . " city, state, country_name, zipcode, created_at, import_queue_item_id) ";
        $select_sql = " SELECT user_id, address_line_1, address_line_2, address_line_3, "
            . " city, state, country_name, zipcode, NOW(), {$this->importQueueItem->import_queue_item_id} AS import_queue_item_id "
            . " FROM {$this->importQueueItem->temp_table_name} AS tmp ";
        $select_sql .= " WHERE tmp.{$validate_is_valid_field_name} = 1 ";

        $this->tempTableService->executeUpdateInBatches($insert_sql . $select_sql, $this->pk_column_name, $this->batch_size, $total_records );
    }

    private function insertIntoUserSettings(){
        $validate_is_valid_field_name = $this->getAllFields()['validate_is_valid']->getColumnName();
        $total_records = $this->importQueueItem->additionalDetails->total_records;
        $insert_sql = "INSERT INTO `user_settings`(user_id, created_at, import_queue_item_id) ";
        $select_sql = " SELECT user_id, NOW(), {$this->importQueueItem->import_queue_item_id} AS import_queue_item_id "
            . " FROM {$this->importQueueItem->temp_table_name} AS tmp ";
        $select_sql .= " WHERE tmp.{$validate_is_valid_field_name} = 1 ";

        $this->tempTableService->executeUpdateInBatches($insert_sql . $select_sql, $this->pk_column_name, $this->batch_size, $total_records );
    }

    private function insertIntoGroupMembers(){
        $validate_is_valid_field_name = $this->getAllFields()['validate_is_valid']->getColumnName();
        $total_records = $this->importQueueItem->additionalDetails->total_records;
        $insert_sql = "INSERT INTO `group_members`(group_id, member_user_id, email, role_id, invitation_accepted, is_active, created_at, import_queue_item_id) ";
        $select_sql = " SELECT group_id, user_id, email, coins_for_college_role_id,"
            . " 1 AS invitation_accepted, 1 AS is_active,  NOW(), "
            . " {$this->importQueueItem->import_queue_item_id} AS import_queue_item_id "
            . " FROM {$this->importQueueItem->temp_table_name} AS tmp";
        $select_sql .= " WHERE tmp.{$validate_is_valid_field_name} = 1 ";

        $this->tempTableService->executeUpdateInBatches($insert_sql . $select_sql, $this->pk_column_name, $this->batch_size, $total_records );
    }

    // TODO: yet to implement
    private function insertIntoRoleUser(){
        $validate_is_valid_field_name = $this->getAllFields()['validate_is_valid']->getColumnName();
        $total_records = $this->importQueueItem->additionalDetails->total_records;
        $insert_sql = "INSERT INTO `role_user`( role_id, user_id, user_type, team_id, import_queue_item_id) ";
        $select_sql = " SELECT role_id, user_id, 'App\\Models\\User' AS user_type, team_id, {$this->importQueueItem->import_queue_item_id} AS import_queue_item_id "
            . " FROM {$this->importQueueItem->temp_table_name} AS tmp";
        $select_sql .= " WHERE tmp.{$validate_is_valid_field_name} = 1 ";

        $this->tempTableService->executeUpdateInBatches($insert_sql . $select_sql, $this->pk_column_name, $this->batch_size, $total_records );
    }

    private function insertIntoSpWallet(){
        $validate_is_valid_field_name = $this->getAllFields()['validate_is_valid']->getColumnName();
        $total_records = $this->importQueueItem->additionalDetails->total_records;
        $insert_sql = "INSERT INTO `sp_wallet`(user_id, wallet_name, wallet_uid, created_at, import_queue_item_id) ";
        $select_sql = " SELECT user_id, 'SP Wallet' AS wallet_name, sp_wallet_uid, NOW(),"
            . " {$this->importQueueItem->import_queue_item_id} AS import_queue_item_id "
            . " FROM {$this->importQueueItem->temp_table_name} AS tmp";
        $select_sql .= " WHERE tmp.{$validate_is_valid_field_name} = 1 ";

        $this->tempTableService->executeUpdateInBatches($insert_sql . $select_sql, $this->pk_column_name, $this->batch_size, $total_records );
    }

    protected function populateSpWalletIdToTempTable(){
        $field = $this->getAllFields()['sp_wallet_uid'];
        $ref_id_field = $this->getAllFields()['sp_wallet_id'];
        $validate_is_valid_field_name = $this->getAllFields()['validate_is_valid']->getColumnName();

        $total_records = $this->importQueueItem->additionalDetails->total_records;

        $validationQuery = "UPDATE {$this->importQueueItem->temp_table_name} AS tmp ";
        $validationQuery .= " LEFT JOIN `sp_wallet` AS wallet  ";
        $validationQuery .= " ON wallet.wallet_uid = tmp.{$field->getColumnName()} " ;
        $validationQuery .= " SET tmp.{$ref_id_field->getColumnName()} = wallet.sp_wallet_id ";
        $validationQuery .= " WHERE wallet.sp_wallet_id IS NOT NULL AND wallet.deleted_at IS NULL ";
        $validationQuery .= " AND tmp.{$validate_is_valid_field_name} = 1 ";

        Log::debug("Running Update Query: ". $validationQuery);
        $this->tempTableService->executeUpdateInBatches($validationQuery,
            'tmp.'.$this->pk_column_name, $this->batch_size, $total_records);
        return ['error' => false, 'error_message' => ''];
    }

    private function insertIntoTuitWallet(){
        $validate_is_valid_field_name = $this->getAllFields()['validate_is_valid']->getColumnName();
        $total_records = $this->importQueueItem->additionalDetails->total_records;
        $insert_sql = "INSERT INTO `tuit_wallet`(user_id, wallet_name, wallet_uid, created_at, import_queue_item_id) ";
        $select_sql = " SELECT user_id, 'TUIT Wallet', tuit_wallet_uid, NOW(),"
            . " {$this->importQueueItem->import_queue_item_id} AS import_queue_item_id "
            . " FROM {$this->importQueueItem->temp_table_name} AS tmp ";
        $select_sql .= " WHERE tmp.{$validate_is_valid_field_name} = 1 ";

        $this->tempTableService->executeUpdateInBatches($insert_sql . $select_sql, $this->pk_column_name, $this->batch_size, $total_records );
    }

    protected function populateTuitWalletIdToTempTable(){
        $field = $this->getAllFields()['tuit_wallet_uid'];
        $ref_id_field = $this->getAllFields()['tuit_wallet_id'];
        $validate_is_valid_field_name = $this->getAllFields()['validate_is_valid']->getColumnName();

        $total_records = $this->importQueueItem->additionalDetails->total_records;

        $validationQuery = "UPDATE {$this->importQueueItem->temp_table_name} AS tmp ";
        $validationQuery .= " LEFT JOIN `tuit_wallet` AS wallet  ";
        $validationQuery .= " ON wallet.wallet_uid = tmp.{$field->getColumnName()} " ;
        $validationQuery .= " SET tmp.{$ref_id_field->getColumnName()} = wallet.tuit_wallet_id ";
        $validationQuery .= " WHERE wallet.tuit_wallet_id IS NOT NULL AND wallet.deleted_at IS NULL ";
        $validationQuery .= " AND tmp.{$validate_is_valid_field_name} = 1 ";

        Log::debug("Running Update Query: ". $validationQuery);
        $this->tempTableService->executeUpdateInBatches($validationQuery,
            'tmp.'.$this->pk_column_name, $this->batch_size, $total_records);
        return ['error' => false, 'error_message' => ''];
    }

    private function insertIntoStudentSegmentUsers($column_name){
        $validate_is_valid_field_name = $this->getAllFields()['validate_is_valid']->getColumnName();
        $total_records = $this->importQueueItem->additionalDetails->total_records;
        $insert_sql = "INSERT INTO `student_segment_user`(user_id, student_segment_id, created_at, import_queue_item_id) ";
        $select_sql = " SELECT user_id, {$column_name} AS student_segment_id , NOW(), {$this->importQueueItem->import_queue_item_id} AS import_queue_item_id "
            . " FROM {$this->importQueueItem->temp_table_name} AS tmp ";
        $select_sql .= " WHERE tmp.{$validate_is_valid_field_name} = 1 "
            . " AND tmp.{$column_name} IS NOT NULL "
            . " AND tmp.{$column_name} > 0 ";
        Log::debug("Running Insert Query: ". $insert_sql.$select_sql);
        $this->tempTableService->executeUpdateInBatches($insert_sql . $select_sql, $this->pk_column_name, $this->batch_size, $total_records );
    }
}

?>